<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;


class SwitchData extends Model
{
    const UPDATED_AT = null;

    protected $table = 'xfy_switch_data';
    protected $primaryKey = 'id';

    public static function getDataFromRedis(){
        $switches = SwitchModel::where('pid', '>', 0)->pluck('machine_code', 'id')->toArray();
        if (empty($switches)) {
            return;
        }

        $insertData = [];
        foreach ($switches as $id => $ssn) {
            $one['switch_id'] = $id;
            $one['created_time'] = time();
            $jsonArr = self::getLatestRedisData($ssn);
            if (!empty($jsonArr)) {
                $one['data'] = json_encode($jsonArr);
                array_push($insertData, $one);
            }
        }
        if (!empty($insertData)) {
            DB::table('xfy_switch_data')->insert($insertData);
        }
        return;
    }

    public static function getLatestRedisData($key){
        $d = Redis::get($key);
        if (empty($d)) {
            return [];
        }
        $data = json_decode($d, true);
        $jsonArr = [];
        foreach ($data as $key => $value) {
            $jsonArr[$key] = floatval($value);
        }
        return $jsonArr;
    }

    public static function getJsonDataOne($switchId, $fields, $time = 0){
        $sql = 'select ';

        foreach ($fields as $oneField) {
            if ($oneField == 'created_time') {
                $sql .= ' created_time,';
            } else {
                $sql .= "data->'$.".$oneField."' as ".$oneField.',';
            }
        }
        $sql = substr($sql,0,-1);
        $sql .= ' from xfy_switch_data ';

        $sql .= "where switch_id = ?";

        if ($time) {
            $sql .= " and created_time > ? ";
        }
        $sql .= " order by created_time desc ";
        $sql .= " limit 1";
        if ($time) {
            $res = DB::select($sql, [$switchId, $time]);
        } else {
            $res = DB::select($sql, [$switchId]);
        }

        $res = json_decode( json_encode( reset($res)),true);

        return $res;
    }

    public static function getMax($switchId, $field, $start, $end){
        $sql = "select max(data->'$.".$field."') as max from `xfy_switch_data` where `switch_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$switchId, $start, $end]);
        $max = json_decode( json_encode( reset($res)),true);
        if ($max['max']) {
            return round($max['max'],3);
        } else {
            return 0;
        }
    }

    public static function getMin($switchId, $field, $start, $end){
        $sql = "select min(data->'$.".$field."') as min from `xfy_switch_data` where `switch_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$switchId, $start, $end]);
        $min = json_decode( json_encode( reset($res)),true);
        if ($min['min']) {
            return round($min['min'],3);
        } else {
            return 0;
        }
    }

    public static function getAvg($switchId, $field, $start, $end){
        $sql = "select avg(data->'$.".$field."') as avg from `xfy_switch_data` where `switch_id` = ? and `created_time` between ? and ?";
        $res = DB::select($sql, [$switchId, $start, $end]);
        $avg = json_decode( json_encode( reset($res)),true);
        if ($avg['avg']) {
            return round($avg['avg'],3);
        } else {
            return 0;
        }
    }

    public static function getJsonDataList($switchId, $fields, $start, $end = 0){
        $sql = 'select ';

        if (!is_array($fields)) {
            $fields = [$fields];
        }

        foreach ($fields as $oneField) {
            $sql .= "data->'$.".$oneField."' as ".$oneField.',';
        }

        if ($end) {
            $sql .= " `created_time` from `xfy_switch_data` where `switch_id` = ? and `created_time` >= ? and `created_time` <= ? order by `created_time` asc";
            $res = DB::select($sql, [$switchId, $start, $end]);
        } else {
            $sql .= " `created_time` from `xfy_switch_data` where `switch_id` = ? and `created_time` > ? order by `created_time` asc";
            $res = DB::select($sql, [$switchId, $start]);
        }
        return $res;
    }
}